<?php
// +----------------------------------------------------------------------
// | quanli team
// +----------------------------------------------------------------------
// | Copyright (c) 2013-2020 http://www.fistforward.com/ All rights reserved.
// +----------------------------------------------------------------------
// | create by allen.qiang
// +----------------------------------------------------------------------

/**
 * 自动营销操作类 custcareHandler
 * @package  htdocs
 * @subpackage  module/base
 * @create by allen.qiang
 * @version 1.0
 */

class custcareHandler {
    public $Condition;
    public $dbca;

    public function __construct($Condition){
        global $G_PGLOB ;
        $this->Condition = $Condition;
        $this->dbca = $G_PGLOB->DBCA;
    }

    /**
     * 返回给定时间内的周
     * @param $startdate
     * @param $enddate
     * @return array
     */
    public function getWeek($startdate,$enddate)
    {
        //参数不能为空
        if(!empty($startdate) && !empty($enddate)){

            //先把两个日期转为时间戳
            $startdate=strtotime($startdate);
            $enddate=strtotime($enddate);
            //开始日期不能大于结束日期
            if($startdate<=$enddate){
                $end_date=strtotime("next monday",$enddate);
                if(date("w",$startdate)==1){
                    $start_date=$startdate;
                }else{
                    $start_date=strtotime("last monday",$startdate);
                }
                //计算时间差多少周
                $countweek=($end_date-$start_date)/(7*24*3600);
                for($i=0;$i<$countweek;$i++){
                    $sd=date("Y-m-d",$start_date);
                    $ed=strtotime("+ 6 days",$start_date);
                    $eed=date("Y-m-d",$ed);
                    $arr[]=array($sd,$eed);
                    $start_date=strtotime("+ 1 day",$ed);
                }
                return $arr;
            }
        }
    }

    /**
     * 返回各周内的柱形图和饼图数据
     * @param $datel
     * @return mixed
     */
    public function weekwake($datel){
        global $G_PGLOB ;
        $pie_wake = 0;
        $pie_no_wake = 0;
        foreach($datel as $val){
            if($this->Condition == 'wake'){
                $no_wake = $G_PGLOB->DBCA->stmt_query('select count(*) as num from cust_wake where createTime <= ? and wake = ?','si',array($val[1],0));
                $ymd['data']['no_wake'][] = $no_wake[0]->num;//柱形图纵坐标未唤醒
                $wake = $G_PGLOB->DBCA->stmt_query('select count(*) as num from cust_wake where createTime >= ? and wake = ? and createTime <= ?','sis',array($val[0],1,$val[1]));
                $ymd['data']['wake'][] = $wake[0]->num;//柱形图纵坐标唤醒
                $pie_wake += $wake[0]->num;//饼形图唤醒
                $pie_no_wake += $no_wake[0]->num;//饼形图未唤醒
            }elseif($this->Condition == 'repay'){
                $no_wake = $G_PGLOB->DBCA->stmt_query('select bm.bill_totalPaid  from cust_repay cr inner join bill_master bm on cr.bill_no = bm.bill_no  where cr.createTime >= ? and cr.payment = ? and  cr.createTime <= ?','sis',array($val[0],0,$val[1]));
                $pay = 0;
                if($this->is_result($no_wake)){
                    foreach($no_wake as $val_no){
                        $pay += $val_no->bill_totalPaid;
                    }
                }
                $ymd['data']['no_wake'][] = $pay;//柱形图纵坐已付金额
                $wake = $G_PGLOB->DBCA->stmt_query('select bm.bill_totalPaid  from cust_repay cr inner join bill_master bm on cr.bill_no = bm.bill_no  where cr.createTime >= ? and cr.payment = ? and  cr.createTime <= ?','sis',array($val[0],1,$val[1]));
                $nopay = 0;
                if($this->is_result($wake)){
                    foreach($wake as $val_ok){
                        $nopay += $val_ok->bill_totalPaid;
                    }
                }
                $ymd['data']['wake'][] = $nopay;//柱形图纵坐标未付金额
                $pie_wake += $pay;
                $pie_no_wake += $nopay;
            }
            $ymd['pie']['pie_wake'] = $pie_wake;//饼形图纵坐已付金额
            $ymd['pie']['pie_no_wake'] = $pie_no_wake;//饼形图纵坐未付金额
        }
        return  $ymd;
    }

    /**
     * 返回各天内的柱形图和饼图数据
     * @param $start_Date
     * @param $end_Date
     * @return array
     */
    public function dayWake($start_Date,$end_Date){
        global $G_PGLOB ;
        $pie_wake = 0;
        $pie_no_wake = 0;
        $ymd = array();
        $start_Date_str = strtotime($start_Date);
        $end_Date_str = strtotime($end_Date);
        $days=round(($end_Date_str-$start_Date_str)/3600/24) ;
        //柱形图分析数据
        for($i=0;$i<$days;$i++){
            $mDate = date('y-m-d',strtotime('+2 d',strtotime($start_Date)));
            $mDate = strtotime($start_Date);
            $m_Date = $mDate + 3600*24*$i;
            $mDate1 = date('y-m-d',$m_Date);
            $ymd['datel'][] = $mDate1;//横坐标
        }
        foreach($ymd['datel'] as $val){
            $m_Date = strtotime($val) + 3600*24;
            $mDate1 = date('y-m-d',$m_Date);
            if($this->Condition == 'wake'){
                $no_wake = $G_PGLOB->DBCA->stmt_query('select count(*) as num from cust_wake where createTime <= ? and wake = ?','si',array($mDate1,0));
                $ymd['data']['no_wake'][] = $no_wake[0]->num;//纵坐标未唤醒
                $wake = $G_PGLOB->DBCA->stmt_query('select count(*) as num from cust_wake where createTime >= ? and wake = ? and createTime <= ?','sis',array($val,1,$mDate1));
                $ymd['data']['wake'][] = $wake[0]->num;//纵坐标唤醒
                $pie_wake += $wake[0]->num;
                $pie_no_wake += $no_wake[0]->num;
            }elseif($this->Condition == 'repay'){
                $no_wake = $G_PGLOB->DBCA->stmt_query('select bm.bill_totalPaid  from cust_repay cr inner join bill_master bm on cr.bill_no = bm.bill_no  where cr.createTime >= ? and cr.payment = ? and  cr.createTime <= ?','sis',array($val,0,$mDate1));
                $pay = 0;
                if($this->is_result($no_wake)){
                    foreach($no_wake as $val_no){
                        $pay += $val_no->bill_totalPaid;
                    }
                }
                $ymd['data']['no_wake'][] = $pay;//纵坐已付金额
                $wake = $G_PGLOB->DBCA->stmt_query('select bm.bill_totalPaid  from cust_repay cr inner join bill_master bm on cr.bill_no = bm.bill_no  where cr.createTime >= ? and cr.payment = ? and  cr.createTime <= ?','sis',array($val,1,$mDate1));
                $nopay = 0;
                if($this->is_result($wake)){
                    foreach($wake as $val_ok){
                        $nopay += $val_ok->bill_totalPaid;
                    }
                }
                $ymd['data']['wake'][] = $nopay;//纵坐标未付金额
                $pie_wake += $pay;
                $pie_no_wake += $nopay;
            }
        }
        //饼图分析数据
        $ymd['pie']['pie_wake'] = $pie_wake;
        $ymd['pie']['pie_no_wake'] = $pie_no_wake;

        return $ymd;
    }

    /**
     * 返回各月内的柱形图和饼图数据
     * @param $start_Date
     * @param $end_Date
     * @return mixed
     */
    public function monthWake($start_Date,$end_Date){
        global $G_PGLOB;
        $pie_wake = 0;
        $pie_no_wake = 0;
        $start_Date_str = date('y-m',strtotime($start_Date)).'-01';
        $end_Date_str = date('y-m',strtotime('+1 month',strtotime($end_Date))).'-01';
        $start_Date_str = strtotime($start_Date_str);
        $end_Date_str = strtotime($end_Date_str);
        $month=round(($end_Date_str-$start_Date_str)/3600/24/30) ;
        if($month == 0){
                $month = 1;
        }
        for($i=0;$i<$month;$i++){
            $last_mon = date("y-m" , strtotime('+'.$i.' month',$start_Date_str) );
            $ymd['datel'][] = $last_mon;//柱形图横坐标
        }

        foreach($ymd['datel'] as $val){
            $val = $val.'-01';
            $next_val = date('y-m-d',strtotime('+1 month',strtotime($val)));
            if($this->Condition == 'wake'){
                $no_wake = $G_PGLOB->DBCA->stmt_query('select count(*) as num from cust_wake where createTime <= ? and wake = ?','si',array($next_val,0));
                $ymd['data']['no_wake'][] = $no_wake[0]->num;//柱形图纵坐标未唤醒
                $wake = $G_PGLOB->DBCA->stmt_query('select count(*) as num from cust_wake where createTime >= ? and wake = ? and createTime <= ?','sis',array($val,1,$next_val));
                $ymd['data']['wake'][] = $wake[0]->num;//柱形图纵坐标唤醒
                $pie_wake += $wake[0]->num;//饼形图唤醒
                $pie_no_wake += $no_wake[0]->num;//饼形图未唤醒
            }elseif($this->Condition == 'repay'){
                $no_wake = $G_PGLOB->DBCA->stmt_query('select bm.bill_totalPaid  from cust_repay cr inner join bill_master bm on cr.bill_no = bm.bill_no  where cr.createTime >= ? and cr.payment = ? and  cr.createTime <= ?','sis',array($val,0,$next_val));
                $pay = 0;
                if($this->is_result($no_wake)){
                    foreach($no_wake as $val_no){
                        $pay += $val_no->bill_totalPaid;
                    }
                }
                $ymd['data']['no_wake'][] = $pay;//柱形图纵坐已付金额
                $wake = $G_PGLOB->DBCA->stmt_query('select bm.bill_totalPaid  from cust_repay cr inner join bill_master bm on cr.bill_no = bm.bill_no  where cr.createTime >= ? and cr.payment = ? and  cr.createTime <= ?','sis',array($val,1,$next_val));
                $nopay = 0;
                if($this->is_result($wake)){
                    foreach($wake as $val_ok){
                        $nopay += $val_ok->bill_totalPaid;
                    }
                }
                $ymd['data']['wake'][] = $nopay;//柱形图纵坐标未付金额
                $pie_wake += $pay;//饼形图唤醒
                $pie_no_wake += $nopay;//饼形图未唤醒
            }
        }
        //饼图分析数据
        $ymd['pie']['pie_wake'] = $pie_wake;
        $ymd['pie']['pie_no_wake'] = $pie_no_wake;
        return  $ymd;
    }

    /**
     * 返回各年内的柱形图和饼图数据
     * @param $start_Date
     * @param $end_Date
     * @return mixed
     */
    public function yearWake($start_Date,$end_Date){
        global $G_PGLOB;
        $pie_wake = 0;
        $pie_no_wake = 0;
        $start_Date_y = date('y',strtotime($start_Date));
        $end_Date_y = date('y',strtotime($end_Date));
        $year = $end_Date_y-$start_Date_y+1;
        for($i=0;$i<$year;$i++){
            $last_year = $start_Date_y + $i;
            $ymd['datel'][] = $last_year;//横坐标
        }
        foreach($ymd['datel'] as $val){
            $next_val = ($val+1).'-01'.'-01';
            $val = $val.'-01'.'-01';
            if($this->Condition == 'wake'){
                $no_wake = $G_PGLOB->DBCA->stmt_query('select count(*) as num from cust_wake where createTime <= ? and wake = ?','si',array($next_val,0));
                $ymd['data']['no_wake'][] = $no_wake[0]->num;//柱形图纵坐标未唤醒
                $wake = $G_PGLOB->DBCA->stmt_query('select count(*) as num from cust_wake where createTime >= ? and wake = ? and createTime <= ?','sis',array($val,1,$next_val));
                $ymd['data']['wake'][] = $wake[0]->num;//柱形图纵坐标唤醒
                $pie_wake += $wake[0]->num;//饼形图唤醒
                $pie_no_wake += $no_wake[0]->num;//饼形图未唤醒
            }elseif($this->Condition == 'repay'){
                $no_wake = $G_PGLOB->DBCA->stmt_query('select bm.bill_totalPaid  from cust_repay cr inner join bill_master bm on cr.bill_no = bm.bill_no  where cr.createTime >= ? and cr.payment = ? and  cr.createTime <= ?','sis',array($val,0,$next_val));
                $pay = 0;
                if($this->is_result($no_wake)){
                    foreach($no_wake as $val_no){
                        $pay += $val_no->bill_totalPaid;
                    }
                }
                $ymd['data']['no_wake'][] = $pay;//纵坐已付金额
                $wake = $G_PGLOB->DBCA->stmt_query('select bm.bill_totalPaid  from cust_repay cr inner join bill_master bm on cr.bill_no = bm.bill_no  where cr.createTime >= ? and cr.payment = ? and  cr.createTime <= ?','sis',array($val,1,$next_val));
                $nopay = 0;
                if($this->is_result($wake)){
                    foreach($wake as $val_ok){
                        $nopay += $val_ok->bill_totalPaid;
                    }
                }
                $ymd['data']['wake'][] = $nopay;//纵坐标未付金额
                $pie_wake += $pay;//饼形图已付金额
                $pie_no_wake += $nopay;//饼形图未付金额
            }
        }
        //饼图分析数据
        $ymd['pie']['pie_wake'] = $pie_wake;
        $ymd['pie']['pie_no_wake'] = $pie_no_wake;
        return  $ymd;
    }

    /**
     * 判断数组结果是否为空
     * @param $obj
     * @return bool
     */
    public function  is_result($obj){
        $num = count($obj);
        if($num>0){
            return true;
        }else{
            return false;
        }
    }

    /**
     * 生日关怀列表
     * @param $_REQUEST
     * @return mixed
     */
    public function birthdaycare($_REQUEST){
        //sql字符串
        $sql = ' AND cs.autoType = ?';

        //参数类型字符串
        $params_type = 'i';

        //参数值数组
        $params_array = array();
        $params_array [] = 1;
        //发送时间
        if (isset($_REQUEST['start-Date']) &&  !empty($_REQUEST['start-Date']) ) {
            $sql .= ' AND cs.sendtime > ?';
            $params_type .= 's';
            $params_array[] = trim($_REQUEST['start-Date']);
        }
        if (isset($_REQUEST['end-Date']) &&  !empty($_REQUEST['end-Date']) ) {
            $sql .= ' AND cs.sendtime < ?';
            $params_type .= 's';
            $params_array[] = trim($_REQUEST['end-Date']);
        }
        //会员名 姓名 手机 电子邮箱（查询条件）
        if(isset($_REQUEST['roleGroup'])){
            switch($_REQUEST['roleGroup']){
                case 1:
                    if (isset($_REQUEST['stext']) &&  !empty($_REQUEST['stext']) ) {
                        $sql .= ' AND cm.custID = ?';
                        $params_type .= 's';
                        $params_array[] = trim($_REQUEST['stext']);
                    }
                    break;
                case 2:
                    if (isset($_REQUEST['stext']) &&  !empty($_REQUEST['stext']) ) {
                        $sql .= ' AND cm.custname = ?';
                        $params_type .= 's';
                        $params_array[] = trim($_REQUEST['stext']);
                    }
                    break;
                case 3:
                    if (isset($_REQUEST['stext']) &&  !empty($_REQUEST['stext']) ) {
                        $sql .= ' AND cm.mobile = ?';
                        $params_type .= 's';
                        $params_array[] = trim($_REQUEST['stext']);
                    }
                    break;
                case 4:
                    if (isset($_REQUEST['stext']) &&  !empty($_REQUEST['stext']) ) {
                        $sql .= ' AND cm.email = ?';
                        $params_type .= 's';
                        $params_array[] = trim($_REQUEST['stext']);
                    }
                    break;


            }
        }
        if ($sql) {
            $sql = 'WHERE ' . substr($sql, 4);
        }

        //分页字符串
        $limit = '';

        if (isset($_REQUEST['rp']) && $_REQUEST['rp'] > 1) {

            //排序
            if (isset($_REQUEST['sortname']) && !empty($_REQUEST['sortname']))
                $limit .= sprintf(' ORDER BY tr.%s %s', $_REQUEST['sortname'], $_REQUEST['sortorder']);

            //分页
            if (!isset($_REQUEST['page']) || !is_numeric($_REQUEST['page']) || $_REQUEST['page'] <= 0)
                $_REQUEST['page'] = 1;

            $limit .= sprintf(' LIMIT %d, %d', ($_REQUEST['page'] - 1) * $_REQUEST['rp'], $_REQUEST['rp']);

        } else {

            //排序
            if (isset($_REQUEST['sortname']) && !empty($_REQUEST['sortname']))
                $limit .= sprintf(' ORDER BY tr.%s %s', $_REQUEST['sortname'], $_REQUEST['sortorder']);

        }

        //统计数量
        $search_sql  = 'SELECT count(*) as num FROM cust_sendrecorde cs inner join cust_master cm on cs.pcustID = cm.pcustID  left join cust_buy_behaviour cbb on cm.pcustID = cbb.pcustID ' . $sql;

        $count_array = $this->dbca->stmt_query($search_sql, $params_type, $params_array);
        if( $count_array[0]->num == 0 ){

            $result_array = array(
                'page'  => $_REQUEST['page'],
                'total' => 0,
                'rows'  => array()
            );

            $result_all['result_array'] = $result_array;
            return $result_all;

        }
        $search_item = 'DISTINCT cs.pcustID, cm.custID,cm.custname,cm.birthday,cm.email,cm.mobile,cbb.allBillNum,cm.rankName,cm.createTime,cs.sendtime  ';
        //查询分类信息
        $search_sql = 'SELECT '.$search_item.' FROM cust_sendrecorde cs inner join cust_master cm on cs.pcustID = cm.pcustID  left join cust_buy_behaviour cbb on cm.pcustID = cbb.pcustID ' . $sql . $limit;

        $result     = $this->dbca->stmt_query($search_sql, $params_type, $params_array);


        //查询结果

        $result_array = array(
            'page'  => $_REQUEST['page'],
            'total' => $count_array[0]->num,
            'rows'  => array()
        );

        $result_all['result_array'] = $result_array;

        foreach($result as $val){
            $result_all['result'][] = array(
                'pcustID' => $val->pcustID,
                'custID' => $val->custID,
                'custname' => $val->custname,                                                    //会员名
                'birthday' => $val->birthday,
                'email' => $val->email,
                'mobile' => $val->mobile,                                                        //邮件
                'allBillNum' => $val->allBillNum,                                                         //卖家昵称
                'rankName' => $val->rankName,                                                       //评价等级
                'createTime' => $val->createTime,                                                     //评价时间
                'sendtime' => $val->sendtime
            );
        }
        return $result_all;
    }

    /**
     * 等级升级提醒记录
     * @param $_REQUEST
     * @return mixed
     */
    public function custup($_REQUEST){
        //sql字符串
        $sql = ' AND cs.autoType = ?';

        //参数类型字符串
        $params_type = 'i';

        //参数值数组
        $params_array = array();
        $params_array [] = 2;
        //发送时间
        if (isset($_REQUEST['startDate']) &&  !empty($_REQUEST['startDate']) ) {
            $sql .= ' AND cs.sendtime > ?';
            $params_type .= 's';
            $params_array[] = trim($_REQUEST['startDate']);
        }
        if (isset($_REQUEST['endDate']) &&  !empty($_REQUEST['endDate']) ) {
            $sql .= ' AND cs.sendtime < ?';
            $params_type .= 's';
            $params_array[] = trim($_REQUEST['endDate']);
        }
        //会员名 姓名 手机 电子邮箱
        if(isset($_REQUEST['roleGroup'])){
            switch($_REQUEST['roleGroup']){
                case 1:
                    if (isset($_REQUEST['stext']) &&  !empty($_REQUEST['stext']) ) {
                        $sql .= ' AND cm.custID = ?';
                        $params_type .= 's';
                        $params_array[] = trim($_REQUEST['stext']);
                    }
                    break;
                case 2:
                    if (isset($_REQUEST['stext']) &&  !empty($_REQUEST['stext']) ) {
                        $sql .= ' AND cm.custname = ?';
                        $params_type .= 's';
                        $params_array[] = trim($_REQUEST['stext']);
                    }
                    break;
                case 3:
                    if (isset($_REQUEST['stext']) &&  !empty($_REQUEST['stext']) ) {
                        $sql .= ' AND cm.mobile = ?';
                        $params_type .= 's';
                        $params_array[] = trim($_REQUEST['stext']);
                    }
                    break;
                case 4:
                    if (isset($_REQUEST['stext']) &&  !empty($_REQUEST['stext']) ) {
                        $sql .= ' AND cm.email = ?';
                        $params_type .= 's';
                        $params_array[] = trim($_REQUEST['stext']);
                    }
                    break;
            }
        }
        if ($sql) {
            $sql = 'WHERE ' . substr($sql, 4);
        }

        //分页字符串
        $limit = '';

        if (isset($_REQUEST['rp']) && $_REQUEST['rp'] > 1) {

            //排序
            if (isset($_REQUEST['sortname']) && !empty($_REQUEST['sortname']))
                $limit .= sprintf(' ORDER BY tr.%s %s', $_REQUEST['sortname'], $_REQUEST['sortorder']);

            //分页
            if (!isset($_REQUEST['page']) || !is_numeric($_REQUEST['page']) || $_REQUEST['page'] <= 0)
                $_REQUEST['page'] = 1;

            $limit .= sprintf(' LIMIT %d, %d', ($_REQUEST['page'] - 1) * $_REQUEST['rp'], $_REQUEST['rp']);

        } else {

            //排序
            if (isset($_REQUEST['sortname']) && !empty($_REQUEST['sortname']))
                $limit .= sprintf(' ORDER BY tr.%s %s', $_REQUEST['sortname'], $_REQUEST['sortorder']);

        }

        //统计数量
        $search_sql  = 'SELECT count(*) as num FROM cust_sendrecorde cs inner join cust_master cm on cs.pcustID = cm.pcustID  left join cust_buy_behaviour cbb on cm.pcustID = cbb.pcustID ' . $sql;
        $count_array = $this->dbca->stmt_query($search_sql, $params_type, $params_array);

        if( $count_array[0]->num == 0 ){

            $result_array = array(
                'page'  => $_REQUEST['page'],
                'total' => 0,
                'rows'  => array()
            );
            $result_all['result_array'] = $result_array;
            return $result_all;
        }

        $search_item = 'DISTINCT cs.pcustID, cm.custID,cm.custname,cm.email,cm.mobile,cm.provinceName,cbb.allBillNum,cm.rankName,cm.createTime,cs.sendtime ';
        //查询分类信息
        $search_sql = 'SELECT '.$search_item.' FROM cust_sendrecorde cs inner join cust_master cm on cs.pcustID = cm.pcustID  left join cust_buy_behaviour cbb on cm.pcustID = cbb.pcustID ' . $sql . $limit;

        $result     = $this->dbca->stmt_query($search_sql, $params_type, $params_array);
        //查询结果
        $result_array = array(
            'page'  => $_REQUEST['page'],
            'total' => $count_array[0]->num,
            'rows'  => array()
        );

        $result_all['result_array'] = $result_array;

        foreach($result as $val){
            $result_all['result'][] = array(
                'pcustID' => $val->pcustID,
                'custID' => $val->custID,
                'custname' => $val->custname,
                'email' =>$val->email,
                'mobile' => $val->mobile,
                'provinceName' => $val->provinceName,
                'allBillNum' => $val->allBillNum,
                'rankName' => $val->rankName,
                'createTime' => $val->createTime,
                'sendtime' => $val->sendtime
            );
        }
        return $result_all;
    }

    /**
     * 休眠会员激活记录
     * @param $_REQUEST
     * @return mixed
     */
    public function memactive($_REQUEST){
        //sql字符串
        $sql = ' AND cs.autoType = ?';

        //参数类型字符串
        $params_type = 'i';

        //参数值数组
        $params_array = array();
        $params_array [] = 3;
        //发送时间
        if (isset($_REQUEST['startDate']) &&  !empty($_REQUEST['startDate']) ) {
            $sql .= ' AND cs.sendtime > ?';
            $params_type .= 's';
            $params_array[] = trim($_REQUEST['startDate']);
        }
        if (isset($_REQUEST['endDate']) &&  !empty($_REQUEST['endDate']) ) {
            $sql .= ' AND cs.sendtime < ?';
            $params_type .= 's';
            $params_array[] = trim($_REQUEST['endDate']);
        }
        //会员名 姓名 手机 电子邮箱（查询条件）
        if(isset($_REQUEST['roleGroup'])){
            switch($_REQUEST['roleGroup']){
                case 1:
                    if (isset($_REQUEST['stext']) &&  !empty($_REQUEST['stext']) ) {
                        $sql .= ' AND cm.custID = ?';
                        $params_type .= 's';
                        $params_array[] = trim($_REQUEST['stext']);
                    }
                    break;
                case 2:
                    if (isset($_REQUEST['stext']) &&  !empty($_REQUEST['stext']) ) {
                        $sql .= ' AND cm.custname = ?';
                        $params_type .= 's';
                        $params_array[] = trim($_REQUEST['stext']);
                    }
                    break;
                case 3:
                    if (isset($_REQUEST['stext']) &&  !empty($_REQUEST['stext']) ) {
                        $sql .= ' AND cm.mobile = ?';
                        $params_type .= 's';
                        $params_array[] = trim($_REQUEST['stext']);
                    }
                    break;
                case 4:
                    if (isset($_REQUEST['stext']) &&  !empty($_REQUEST['stext']) ) {
                        $sql .= ' AND cm.email = ?';
                        $params_type .= 's';
                        $params_array[] = trim($_REQUEST['stext']);
                    }
                    break;
            }
        }
        if ($sql) {
            $sql = 'WHERE ' . substr($sql, 4);
        }

        //分页字符串
        $limit = '';

        if (isset($_REQUEST['rp']) && $_REQUEST['rp'] > 1) {

            //排序
            if (isset($_REQUEST['sortname']) && !empty($_REQUEST['sortname']))
                $limit .= sprintf(' ORDER BY tr.%s %s', $_REQUEST['sortname'], $_REQUEST['sortorder']);

            //分页
            if (!isset($_REQUEST['page']) || !is_numeric($_REQUEST['page']) || $_REQUEST['page'] <= 0)
                $_REQUEST['page'] = 1;

            $limit .= sprintf(' LIMIT %d, %d', ($_REQUEST['page'] - 1) * $_REQUEST['rp'], $_REQUEST['rp']);

        } else {

            //排序
            if (isset($_REQUEST['sortname']) && !empty($_REQUEST['sortname']))
                $limit .= sprintf(' ORDER BY tr.%s %s', $_REQUEST['sortname'], $_REQUEST['sortorder']);

        }

        //统计数量
        $search_sql  = 'SELECT count(*) as num FROM cust_sendrecorde cs inner join cust_master cm on cs.pcustID = cm.pcustID  left join cust_buy_behaviour cbb on cm.pcustID = cbb.pcustID ' . $sql;
        $count_array = $this->dbca->stmt_query($search_sql, $params_type, $params_array);

        if( $count_array[0]->num == 0 ){

            $result_array = array(
                'page'  => $_REQUEST['page'],
                'total' => 0,
                'rows'  => array()
            );

            $result_all['result_array'] = $result_array;
            return $result_all;

        }

        //查询分类信息
        $search_sql = 'SELECT * FROM cust_sendrecorde cs inner join cust_master cm on cs.pcustID = cm.pcustID  left join cust_buy_behaviour cbb on cm.pcustID = cbb.pcustID ' . $sql . $limit;

        $result     = $this->dbca->stmt_query($search_sql, $params_type, $params_array);
        //查询结果
        $result_array = array(
            'page'  => $_REQUEST['page'],
            'total' => $count_array[0]->num,
            'rows'  => array()
        );
        $result_all['result_array'] = $result_array;

        foreach($result as $val){
            $result_all['result'][] = array(
                'pcustID' => $val->pcustID,
                'custID' => $val->custID,
                'custname' => $val->custname,                                                    //会员名
                'email' => $val->email,
                'mobile' => $val->mobile,
                'allBillNum' => $val->allBillNum,                                                        //邮件                                                     //卖家昵称
                'rankName' => $val->rankName,                                                       //评价等级
                'latestBuyTime' => $val->latestBuyTime,                                                     //评价时间
                'sendtime' => $val->sendtime
            );
        }
        return $result_all;
    }
    public function dunnorders($_REQUEST){
        //sql字符串
        $sql = ' AND cs.autoType = ?';

        //参数类型字符串
        $params_type = 'i';

        //参数值数组
        $params_array = array();
        $params_array [] = 4;
        //发送时间
        if (isset($_REQUEST['startDate']) &&  !empty($_REQUEST['startDate']) ) {
            $sql .= ' AND cs.sendtime > ?';
            $params_type .= 's';
            $params_array[] = trim($_REQUEST['startDate']);
        }
        if (isset($_REQUEST['endDate']) &&  !empty($_REQUEST['endDate']) ) {
            $sql .= ' AND cs.sendtime < ?';
            $params_type .= 's';
            $params_array[] = trim($_REQUEST['endDate']);
        }
        //会员名 姓名 手机 电子邮箱
        if(isset($_REQUEST['roleGroup'])){
            switch($_REQUEST['roleGroup']){
                case 1:
                    if (isset($_REQUEST['stext']) &&  !empty($_REQUEST['stext']) ) {
                        $sql .= ' AND cm.custID = ?';
                        $params_type .= 's';
                        $params_array[] = trim($_REQUEST['stext']);
                    }
                    break;
                case 2:
                    if (isset($_REQUEST['stext']) &&  !empty($_REQUEST['stext']) ) {
                        $sql .= ' AND cm.custname = ?';
                        $params_type .= 's';
                        $params_array[] = trim($_REQUEST['stext']);
                    }
                    break;
                case 3:
                    if (isset($_REQUEST['stext']) &&  !empty($_REQUEST['stext']) ) {
                        $sql .= ' AND cm.mobile = ?';
                        $params_type .= 's';
                        $params_array[] = trim($_REQUEST['stext']);
                    }
                    break;
                case 4:
                    if (isset($_REQUEST['stext']) &&  !empty($_REQUEST['stext']) ) {
                        $sql .= ' AND cm.email = ?';
                        $params_type .= 's';
                        $params_array[] = trim($_REQUEST['stext']);
                    }
                    break;


            }
        }
        if ($sql) {
            $sql = 'WHERE ' . substr($sql, 4);
        }

        //分页字符串
        $limit = '';

        if (isset($_REQUEST['rp']) && $_REQUEST['rp'] > 1) {

            //排序
            if (isset($_REQUEST['sortname']) && !empty($_REQUEST['sortname']))
                $limit .= sprintf(' ORDER BY tr.%s %s', $_REQUEST['sortname'], $_REQUEST['sortorder']);

            //分页
            if (!isset($_REQUEST['page']) || !is_numeric($_REQUEST['page']) || $_REQUEST['page'] <= 0)
                $_REQUEST['page'] = 1;

            $limit .= sprintf(' LIMIT %d, %d', ($_REQUEST['page'] - 1) * $_REQUEST['rp'], $_REQUEST['rp']);

        } else {

            //排序
            if (isset($_REQUEST['sortname']) && !empty($_REQUEST['sortname']))
                $limit .= sprintf(' ORDER BY tr.%s %s', $_REQUEST['sortname'], $_REQUEST['sortorder']);

        }
        //统计数量
        $search_sql  = 'SELECT COUNT(*) AS num FROM cust_sendrecorde cs inner join cust_master cm on cs.pcustID = cm.pcustID ' . $sql;
        $count_array = $this->dbca->stmt_query($search_sql, $params_type, $params_array);

        if( $count_array[0]->num == 0 ){

            $result_array = array(
                'page'  => $_REQUEST['page'],
                'total' => 0,
                'rows'  => array()
            );
            $result_all['result_array'] = $result_array;
            return $result_all;

        }
        $search_item = 'DISTINCT cs.pcustID, cm.custID,cm.custname,cm.email,cm.mobile,cm.rankName,bp.payTypeName,bm.send_region_name,bp.createTime,cs.sendtime';
        //查询分类信息
        $search_sql = 'SELECT '.$search_item.' FROM cust_sendrecorde cs inner join cust_master cm on cs.pcustID = cm.pcustID
       left join bill_master bm on cs.bill_no = bm.bill_no  left join bill_pay bp on cs.bill_no = bp.bill_no
         ' . $sql . $limit;

        $result     = $this->dbca->stmt_query($search_sql, $params_type, $params_array);
        //查询结果
        $result_array = array(
            'page'  => $_REQUEST['page'],
            'total' => $count_array[0]->num,
            'rows'  => array()
        );

        $result_all['result_array'] = $result_array;

        foreach($result as $val){
            $result_all['result'][] = array(
                'pcustID' => $val->pcustID,
                'custID' => $val->custID,
                'custname' => $val->custname,                                                    //会员名
                'email' => $val->email,
                'mobile' => $val->mobile,                                                        //邮件                                                      //卖家昵称
                'rankName' => $val->rankName,
                'payTypeName' => $val->payTypeName,                                                     //评价时间
                'send_region_name' => $val->send_region_name,
                'createTime' => $val->createTime,                                                     //评价时间
                'sendtime' => $val->sendtime
            );
        }
        return $result_all;
    }
}
